package com.wzy.未封装Jdbc;

import java.sql.*;
import java.util.Date;

public class Jdbc_news_category {
    public static void main(String[] args) throws SQLException {
        Jdbc_news_category jdbc1 = new Jdbc_news_category();
        //根据id查询信息
//        jdbc1.selectById(4);
        //根据id修改信息
//        int guoji = jdbc1.updataById("guoji", 2);
//        if(guoji>0){
//            System.out.println("修改成功！");
//        }
        //根据id删除信息
//        int r = jdbc1.deleteById(7);
//        if(r>0){
//            System.out.println("删除成功！");
//        }

        //新增信息
        int r = jdbc1.createNews(7,"科技",new Date());
        if(r>0){
            System.out.println("新增成功！");
        }
    }
    //新增信息
    public int createNews(int id,String name,Date date) throws SQLException {
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/newsdb?characterEncoding=UTF-8";
            String username = "root";
            String password = "wzy123518";
            connection=DriverManager.getConnection(url,username,password);
            String sql = "INSERT INTO `news_category`(`id`,`name`,`createDate`) values(?,?,?)";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,name);
            preparedStatement.setTimestamp(3,new Timestamp(date.getTime()));
            rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            preparedStatement.close();
            connection.close();
        }


        return rows;
    }

    //根据id删除信息
    public int deleteById(int id)throws  SQLException{
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "DELETE FROM `news_category`  WHERE id=?";
            //3、创建SQL执行器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);

            rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            preparedStatement.close();
            connection.close();
        }
        return rows;
    }

    //根据id修改信息
    public int updataById(String name, int id)throws  SQLException{
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "UPDATE `news_category` SET name=? WHERE id=?";
            //3、创建SQL执行器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,id);

             rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            preparedStatement.close();
            connection.close();
        }
        return rows;
    }

    //根据id查询信息
    public  void selectById(int id) throws SQLException{
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Date date = new Date();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "SELECT * FROM `news_category` WHERE id=?";
            //3、创建SQL执行器
             preparedStatement = connection.prepareStatement(sql);
             preparedStatement.setInt(1,id);
            resultSet =  preparedStatement.executeQuery();

            //5、遍历输出sql执行器返回查询数据的结果集
            while(resultSet.next()){
                int id1 = resultSet.getInt(1);
                String name = resultSet.getString(2);
                java.util.Date date1 = resultSet.getDate(3);
                System.out.println("id："+id1+"======名称："+name+"======创建日期："+date1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }
    }
}
